<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@Import Namespace="System.Data.Common" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Writing Data from a DataSet to an XML File</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Writing Data from a DataSet to an XML File</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server" />

<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>SELECT command: <b><span id="outSelect" runat="server"></span></b></div><p />
<div id="outMessage" runat="server">&nbsp;</div>

<script language="VB" runat="server">

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   Dim strConnect As String
   strConnect = ctlConnectStrings.OLEDBConnectionString
   outConnect.innerText = strConnect 'and display it

   'specify the SELECT statement to extract the data
   Dim strSelect As String
   strSelect = "SELECT BookList.*, BookAuthors.FirstName, BookAuthors.LastName " _
             & "FROM BookList INNER JOIN BookAuthors ON BookList.ISBN = BookAuthors.ISBN " _
             & "WHERE BookList.ISBN LIKE '18610033%'"
   outSelect.innerText = strSelect   'and display it

   'declare a variable to hold a DataSet object
   Dim objDataSet As New DataSet

   Try

      'create a new OleDbConnection object using the connection string
      Dim objConnect As New OleDbConnection(strConnect)

      'create a new OleDbDataAdapter using the connection object and select statement
      Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect)

      'fill the dataset with data from the DataSetCommand object
      objDataAdapter.Fill(objDataSet, "Books")

   Catch objError As Exception

      'display error details
      outMessage.innerHTML = "<b>* Error while accessing data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'now we're ready to save the DataSet contents to an XML disk file

   Try

      'use the path to the current virtual application
      Dim strVirtualPath As String = Request.ApplicationPath & "XML-from-DataSet.xml"
      Dim strVSchemaPath As String = Request.ApplicationPath & "Schema-from-DataSet.xsd"

      'write the data and schema from the DataSet to an XML document on disk
      'must use the Physical path to the file not the Virtual path
      objDataSet.WriteXML(Request.MapPath(strVirtualPath))
      outMessage.innerHTML = "Written file: <b><a href=" & Chr(34) & strVirtualPath _
                & Chr(34) & ">" & strVirtualPath & "</a></b><br />"
      objDataSet.WriteXMLSchema(Request.MapPath(strVSchemaPath))
      outMessage.innerHTML += "Written file: <b><a href=" & Chr(34) & strVSchemaPath _
                & Chr(34) & ">" & strVSchemaPath & "</a></b>"

   Catch objError As Exception

      'display error details
      outMessage.innerHTML = "<b>* Error while writing disk file</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

End Sub
</script>


<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
